#!/usr/bin/env python
from pysqlite2 import dbapi2 as sqlite

"""DBQueue - A Queue implementation using a database for persistent data."""

mem = ":memory:"
def_db = "main"
init_sql = """CREATE TABLE q(pos INTEGER, object BLOB);
CREATE TABLE cnt(table TEXT, count INTEGER);
INSERT INTO cnt(table, count) values("q",0);
CREATE TRIGGER inc_q AFTER INSERT ON q 
BEGIN UPDATE cnt SET count = count + 1 WHERE table = "q";
END;"""
len_sql = 'SELECT FROM counts WHERE table = "q";'
getitem_sql = 'SELECT object FROM %(db).q WHERE id=?;'
setitem_sql = 'UPDATE q SET object = ? WHERE id = ?;'
deleteitem_sql = 'DELETE FROM q WHERE id = ?;'
contains_sql = 'SELECT id FROM q WHERE object = ?;'
getslice_sql = 'SELECT object FROM q WHERE id >= ? AND id < ?;'
setslice_sql = 'UPDATE q SET id = id - ? WHERE id = ?;'
delslice_sql = 'DELETE FROM q WHERE id >= ? AND id < ?;'
remove_sql = 'DELETE FROM q WHERE object = ?;'

class DBQueue(Queue):
	"""Queue is a first in first out container. This implementation adds the ability to move the positions of the items in the queue. Defines the usual front, back, pop, and push methods. Implementation uses a database for persitance."""
	def __init__(T, pack, unpack, db=None, set=[]):
		"""Initialize database for use as queue.
		pack - function to convert object to a string
		unpack - function to convert string to an object
		db - file to use as database
		set - initial set to put in queue

Creates a table named q to store each element as a string and a table named cnt to store the rowcount. Then sets q row size to 0 and creates a triggers to decrement and increment cnt. After if initialized with a set, set is added to queue."""
		T.pack = pack
		T.unpack = unpack
		if not db:
			T.con = sqlite.connect(mem)
			T.db = def_db
		else:
			T.con = sqlite.connect(db)
			T.db = db
		init = init_sql 
		T.con.executescript(init_sql)
		if set:
			obj=[]
			id = 0
			for x in set:
				obj.append( (T.pack(x),id) )
			T.con.executemany(setitem_sql, obj)
	def __len__(T):
		"""Retrive number of elements in queue."""
		size = T.con.execute(len_sql)
		return size
	def __str__(T):
		"""String representation of object."""
		return "Queue<SQLite: %(db)s>"
	def __repr__(T):
		"""String representation of an object."""
		T.__str__()
	def __iter__(T):
		"""Allows the use of for loops and iteration through a list."""
		pass #TODO: iterator
	def __getitem__(T, key):
		"""Returns item at position `key'."""
		if(len(T)==0):
			raise EmptySetError("Cannot retrive item at %i, Queue is empty. " % key)
		if key >= len(T):
			raise IndexingError("Cannot retrive item at %i, Invalid key. " % key)
		cursor = T.con.execute(getitem_sql,key)
		tmp = cursor.fetchall()
		obj = T.unpack(tmp[0][0])
		return obj;
	def __setitem__(T, key, value):
		"""Place 'value` at 'key`."""
		size = len(T)
		if (size==0):
			raise EmptySetError("Unable to place item at %i , Queue is empty." % key)
		if key >= size:
			raise IndexingError("Cannot place item at %i, Invalid key. " % key)
		obj = T.pack(value)
		T.con.execute(setitem_sql, obj, key)
	def __delitem__(T, key):
		"""Remove item at 'key`"""
		size=len(t)
		if(size==0):
			raise EmptySetError("Cannot remove item %i, Queue is empty." % key)
		if key >= size:
			raise IndexingError("Cannot remove item at %i, Invalid key. " % key)
		T.con.execute(deleteitem_sql, key)
	def __contains__(T, value):
		"""Returns true if 'value` is in the set."""
		obj = T.pack(value)
		cursor = T.con.execute(contains_sql,obj)
		return len(cursor) > 0
	def __getslice__(T, i, j):
		"""Returns a subset from [i to j)."""
		size = len(T)
		if(size==0):
			raise EmptySetError("Unable to retrive items from %i to %i ; Queue is empty." % (i,j))
		if i >= size or j > size:
			raise IndexingError("Cannot retrive items at %i to %i ; Invalid keys. " % (i,j))
		cursor = T.con.execute(getslice_sql, i, j)
		objects = []
		for obj in cursor.fetchall():
			objects.append(T.unpack(obj[0]))
		return objects
	def __setslice__(T, i, j, values):
		"""Replace subset from [i to j] with 'values`."""
		size = len(T)
		if(size==0):
			raise EmptySetError("Unable to replace items from  %i to %i; Queue is empty." % (i,j))
		if i >= size or j > size:
			raise IndexingError("Cannot retrive item from  %i to %i; Invalid keys. " % (i,j))
		#Insert values
		objects = []
		id = i
		for x in values:
			id = id+1
			objects.append( (T.pack(x), id) )
		T.con.executemany(setitem_sql, objects)
		#delete old elements upto and includeing j
		if id < j:
			T.con.executemany(delslice_sql, id, j+1)
		#re-index elements after j
		if size > j:
			ids = []
			difference = i+len(values)-j
			for x in range(j+1,size):
				ids.append( (difference, x) )
			T.con.executemany(setslice_sql, ids)
	def __delslice__(T,i,j):
		"""Delete from [i to j)."""
		size = len(t)
		if(size==0):
			raise EmptySetError("Unable to delete items from %i to %i; Queue is empty." % (i,j))
		if i >= size or j >= size:
			raise IndexingError("Cannot retrive items from %i to %i; Invalid keys. " % (i,j))
		T.con.execute(delslice_sql, i, j)
	def remove(T, value):
		""" Remove 'value` from queue."""
		T.con.execute(remove_sql,value)
	def push(T,value):
		"""Append 'value` to end of queue."""
		size = len(T)
		obj = T.pack(value)
		T.con.execute(setitem_sql, obj, size)
